﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Yogesh.ExcelXml;
using System.Data.Common;
using System.Data;
using System.IO;
using ICSharpCode.SharpZipLib.Zip;
using System.Text;

//TODO: Add another sheet with help for translators

/// <summary>
/// Exports selected forms and languages to an Excel XML spreadsheet
/// </summary>
public static class ExcelExporter
{
    /// <summary>
    /// Generates, then sends an XML spreadsheet to the browser based on the inputs on the previous page
    /// </summary>
	public static string ExportToExcel(string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, bool zipOutput) {
        Database db = null;
        string error = string.Empty;
        try {
            db = new Database();
            db.OpenConnection();
            
            ExcelXmlWorkbook wb = new ExcelXmlWorkbook();
            List<int> addedIds = new List<int>();
            wb.Properties.Title = "ResourceBlender Resources (" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ")";
            using(Database.Command cmdApplication = db.CreateCommand()) 
            using(Database.Command cmdResources = db.CreateCommand()) {
                cmdApplication.CommandText = "SELECT displayname, defaultLanguage, languages FROM " + db.TablePrefix + "applications WHERE id = @p";
                cmdApplication.AddParameterWithValue("@p", "");
                cmdResources.AddParameterWithValue("@p", "");
                string applicationId = null,
                       applicationName = null,
                       defaultLanguage = null;
                string[] allLanguagesInApplication = null;
                int startingColumn = 1;
                List<string> selectedLanguagesInApplication = new List<string>(); // if just one list of languages is used, there are gaps for columns where a language from another application is not present in the current application
                XmlStyle greenBackground = new XmlStyle(),
                         greyBackground = new XmlStyle();
                greenBackground.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221);
                greenBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Interior.Color = System.Drawing.Color.FromArgb(240, 240, 240);
                greyBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Font.Color = System.Drawing.Color.FromArgb(130, 130, 130);
                for(int i=0;i<applicationIds.Length;i++) {
                    selectedLanguagesInApplication.Clear();
                    applicationId = applicationIds[i];
                    cmdApplication.Parameters["@p"].Value = applicationId;
                    cmdResources.Parameters["@p"].Value = applicationId;
                    using(DbDataReader reader = cmdApplication.ExecuteReader(CommandBehavior.SingleResult)) {
                        while(reader.Read()) {
                            applicationName = reader.GetString(0);
                            defaultLanguage = reader.GetString(1);
                            allLanguagesInApplication = reader.GetString(2).Trim().Split(',');
                        }
                        reader.Close();
                    }
                    if(applicationName == null || applicationName.Length == 0) {
                        throw new ArgumentNullException("Application not found");
                    }
                    selectedLanguagesInApplication.Add(defaultLanguage);

                    int rowIndex = 1;
                    Worksheet sheet = wb[i];
                    sheet.Name = applicationName;
                    Row row = sheet.AddRow();
                    int column = startingColumn + 1;
                    sheet[startingColumn, 0].Value = "Resource ID";
                    Cell headerCell = sheet[column++, 0];
                    headerCell.Value = defaultLanguage;
                    foreach(string lang in allLanguagesInApplication) {
                        if(selectedLanguages.Contains(lang)) {
                            if(lang != defaultLanguage) {
                                selectedLanguagesInApplication.Add(lang);
                                headerCell = sheet[column++, 0];
                                headerCell.Value = lang;
                            }
                        }
                    }
                    sheet.FreezeTopRows = 1;
                    if(selectedLanguagesInApplication.Count == 0) {
                        throw new ArgumentException("No languages found for appliication:" + applicationName);
                    }

                    //sheet.Table.Columns[startingColumn].Width = 200;
                    cmdResources.CommandText = new StringBuilder("SELECT DISTINCT e.resourceid, e.id, r.language, r.translation, r.translatorcomment, c.comment FROM ")
                                                                .Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ")
                                                                .Append(db.TablePrefix).Append("resourcecomments AS c ON c.resourceid = r.resourceid WHERE applicationid = @p AND language IN('")
                                                                .Append(Utils.Implode(selectedLanguagesInApplication, "','", false)).Append("') AND bundle IN('").Append(Utils.Implode(selectedBundles, "','", false)).Append("') ORDER BY e.id").ToString();
                    using(DbDataReader reader = cmdResources.ExecuteReader()) {
                        int lastIdRead = -1;
                        while(reader.Read()) {
                            int resourceId = int.Parse(reader["resourceid"].ToString());
                            Cell cell = null;
                            if(lastIdRead == -1) {
                                if(addedIds.Contains(resourceId)) {
                                    continue;
                                }
                                addedIds.Add(resourceId);
                                lastIdRead = resourceId;
                                cell = sheet[startingColumn, rowIndex];
                                cell.Value = resourceId;
                                cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                cell.Style = greyBackground;
                            } else if(lastIdRead != resourceId) {
                                if(addedIds.Contains(resourceId)) {
                                    continue;
                                }
                                addedIds.Add(resourceId);
                                lastIdRead = resourceId;
                                cell = sheet[startingColumn, ++rowIndex];
                                cell.Value = resourceId;
                                cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                cell.Style = greyBackground;
                            }
                            cell = sheet[selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1, rowIndex];
                            //System.Diagnostics.Debug.WriteLine(reader["language"].ToString()+"\t"+selectedLanguagesInApplication.IndexOf(reader["language"].ToString())+"\t"+selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1);
                            cell.Value = reader["translation"].ToString().Replace("<", "&lt;").Replace(">", "&gt;"); //HttpUtility.HtmlEncode(reader["translation"].ToString());
                            cell.Comment = reader["translatorcomment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                        }
                        reader.Close();
                    }
                    Range defaultColumn = new Range(sheet[startingColumn + 1, 0], sheet[startingColumn + 1, rowIndex]);
                    defaultColumn.Style = greenBackground;
                    Range headerCells = new Range(sheet[startingColumn, 0], sheet[column, 0]);
                    headerCells.Font.Bold = true;
                }
            }
            HttpResponse res = HttpContext.Current.Response;
            using(MemoryStream msXml  = new MemoryStream()) { // holding stream for workbook
                string fileName = "ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm");
                if(zipOutput) {
                    using(MemoryStream msZip = new MemoryStream()) { // output stream for zip
                        ZipOutputStream zipStream = new ZipOutputStream(msZip);
                        zipStream.SetLevel(9);

                        wb.Export(msXml);
                        byte[] buffer = msXml.ToArray();
                        msXml.Close();
                        ZipEntry zipEntry = new ZipEntry(fileName + ".xml");
                        zipEntry.DateTime = DateTime.Now;
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                        zipStream.Finish();

                        res.Clear();
                        res.AddHeader("Content-Type", "binary/octet-stream");
                        res.AddHeader("Content-Length", zipStream.Length.ToString());
                        res.AddHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".zip\"; size=" + zipStream.Length.ToString());
                        res.Flush();

                        res.BinaryWrite(msZip.ToArray());
                        msZip.Close();
                    }
                } else {
                    wb.Export(msXml);
                    res.Clear();
                    res.AddHeader("Content-Type", "binary/octet-stream");
                    res.AddHeader("Content-Length", msXml.Length.ToString());
                    res.AddHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xml\"; size=" + msXml.Length.ToString()); // todo: include date in filename
                    msXml.WriteTo(res.OutputStream);
                }
                res.Flush();
                res.End();
            }
        } catch(Exception ex) {
            error = ex.Message + ex.StackTrace;
        } finally {
            if(db != null) {
                db.CloseConnection();
            }
        }
        return error;
    }
}
